#!/bin/bash

base_path=$(dirname $0)
#实时处理 res_coupon_sum 表
#sh ${base_path}/../sql/select/res_coupon_sum_select.sh  |awk -F '\t' -f ${base_path}/../awk/res_coupon_sum.awk >${base_path}/../sql/replace/res_coupon_sum_replace.sql
#mysql -hhadoop3 -uroot  -proot <${base_path}/../sql/replace/res_coupon_sum_replace.sql
mysql -hhadoop3 -uroot  -proot <<EOF
use mid;
replace into result.res_coupon_sum
select a.day,a.station_id,a.oils,a.oilMoney,a.oilLitre,
        case when c.direct_discount is null then 0 else c.direct_discount end direct_discount,
        case when b.oil_usedmoney is null then 0 else  b.oil_usedmoney end oil_usedmoney,
        case when b.notoil_usedmoney is null then 0 else b.notoil_usedmoney end notoil_usedmoney,
        case when d.ordinary_discount is null then 0 else d.ordinary_discount end ordinary_discount
from (select * from  mid_coupon_all where oils!='非油') as a
left join (select * from  mid_oilnotoil where productcode!='非油') as b
on a.day=b.day and a.station_id=b.use_shop_id and a.oils=b.productcode
left join (select * from  mid_direct  where productcode!='非油') as c
on a.day=c.day and a.station_id=c.use_shop_id and a.oils=c.productcode
left join (select * from  mid_ordinary  where oils!='非油') as d
on a.day=d.day and a.station_id=d.station_id and a.oils=d.oils
union all

select day,station_id,oils,0 oilMoney,0 oilLitre,
        0 direct_discount ,
        0 oil_usedmoney ,
        sum(notoil_usedmoney) notoil_usedmoney,
        sum(ordinary_discount)
        from(
        select a.day,a.use_shop_id station_id,'非油' oils,0 oilMoney,0 oilLitre,
                0 direct_discount ,0 oil_usedmoney ,
                case when a.notoil_usedmoney is null then 0 else a.notoil_usedmoney end notoil_usedmoney,
                0 ordinary_discount
        from mid_oilnotoil as a where productcode='非油'
        union all
        select day,station_id,'非油' oils,0 oilMoney,0 oilLitre,
                0 direct_discount ,0 oil_usedmoney ,0 notoil_usedmoney,ordinary_discount
         from  mid_ordinary where oils='非油'
)as t_notoil group by day,station_id,oils;
EOF
